﻿using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Microsoft.Reporting.WebForms;
using System.Configuration;
using System.Data.SqlClient;

namespace Transport.Admin
{
    public partial class BaoCao : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                doReport();
            }
        }

        public void doReport()
        {
            string connectionString = ConfigurationManager.ConnectionStrings["transportConnectionString"].ConnectionString;
            SqlDataAdapter adapter;
            DataSet myDataSet;

            myDataSet = new DataSet();
            string QueryDriver = @"SELECT tai_xe.ma_tai_xe, SUM(chi_tiet_dat_xe.tien_da_thanh_toan) AS doanh_thu
                                FROM chi_tiet_dat_xe INNER JOIN phan_cong ON chi_tiet_dat_xe.id = phan_cong.ma_dat_xe INNER JOIN tai_xe ON phan_cong.ma_tai_xe = tai_xe.ma_tai_xe
                                GROUP BY tai_xe.ma_tai_xe";
            adapter = new SqlDataAdapter(QueryDriver, connectionString);
            adapter.Fill(myDataSet, "DataTableDriver");
            ReportDataSource rdsDriver = new ReportDataSource("DataSet1", myDataSet.Tables[0]);
            ReportViewerDriver.LocalReport.DataSources.Add(rdsDriver);
            ReportViewerDriver.LocalReport.Refresh();

            myDataSet = new DataSet();
            string QueryMonth = @"SELECT CAST(MONTH(ngay_dat) AS VARCHAR(2)) + '-' + CAST(YEAR(ngay_dat) AS VARCHAR(4)) AS thoi_gian, SUM
                                (tien_da_thanh_toan) AS doanh_thu
                                FROM chi_tiet_dat_xe
                                WHERE (ma_tinh_trang_dat_xe = N'2')
                                GROUP BY CAST(MONTH(ngay_dat) AS VARCHAR(2)) + '-' + CAST(YEAR(ngay_dat) AS VARCHAR(4))
                                ORDER BY thoi_gian";
            adapter = new SqlDataAdapter(QueryMonth, connectionString);
            adapter.Fill(myDataSet, "DataTableMonth");
            ReportDataSource rdsMonth = new ReportDataSource("DataSet1", myDataSet.Tables[0]);
            ReportViewerMonth.LocalReport.DataSources.Add(rdsMonth);
            ReportViewerMonth.LocalReport.Refresh();

            myDataSet = new DataSet();
            string QueryType = @"SELECT dich_vu.ten_dich_vu, SUM(chi_tiet_dat_xe.tien_da_thanh_toan) AS doanh_thu
                                FROM chi_tiet_dat_xe INNER JOIN dich_vu ON chi_tiet_dat_xe.ma_dich_vu = dich_vu.id_dich_vu
                                GROUP BY dich_vu.ten_dich_vu";
            adapter = new SqlDataAdapter(QueryType, connectionString);
            adapter.Fill(myDataSet, "DataTableType");
            ReportDataSource rdsType = new ReportDataSource("DataSet1", myDataSet.Tables[0]);
            ReportViewerType.LocalReport.DataSources.Add(rdsType);
            ReportViewerType.LocalReport.Refresh();

            myDataSet = new DataSet();
            string QueryRegion = @"SELECT mien.ten_mien, SUM(chi_tiet_dat_xe.tien_da_thanh_toan) AS doanh_thu
                                FROM chi_tiet_dat_xe INNER JOIN mien ON chi_tiet_dat_xe.ma_mien = mien.ma_mien
                                GROUP BY mien.ten_mien";
            adapter = new SqlDataAdapter(QueryRegion, connectionString);
            adapter.Fill(myDataSet, "DataTableRegion");
            ReportDataSource rdsRegion = new ReportDataSource("DataSet1", myDataSet.Tables[0]);
            ReportViewerRegion.LocalReport.DataSources.Add(rdsRegion);
            ReportViewerRegion.LocalReport.Refresh();
        }
    }
}